package com.example.edupublisher.mapper;

import com.example.edupublisher.bean.CourseReviewStats;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
//互动域统计mapper接口
@Mapper
public interface CourseStatsMapper {
    //获取某天的互动域数据
    @Select("select course_name,\n" +
            "\tsum(review_total_stars) avgStars ,\n" +
            "\tsum(review_user_count) reviewUserCt, \n" +
            "\t sum(good_review_user_count) goodReviewUserCt,\n" +
            "  sum(good_review_user_count) / sum(review_user_count) goodReviewRate \n" +
            "\tfrom dws_interaction_course_review_window \n" +
            "\twhere DATE_FORMAT(cur_date,\"yyyyMMdd\") = (#{date})\n" +
            "group by course_id, course_name\n" +
            "order by goodReviewUserCt desc\n" +
            "limit 10")
    List<CourseReviewStats> selectCourseReviewStats(@Param("date") Integer date);
}
